﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Collections;

namespace Data.Database
{
    public class Horario
    {
        static private String cadenaConexion = ConfigurationManager.ConnectionStrings["Turnet"].ConnectionString; // @"Data Source=|DataDirectory|\BBDD.sdf";



        public DataSet ConseguirHorarios(DataSet dsprof, string fecha,string DiaSem)
        {
            DataSet dsHora = new DataSet();
            

            try
            {

                SqlConnection conexion = new SqlConnection(cadenaConexion);
                String consulta = "Select H.id_horario,convert(varchar,H.horaincio,108),H.HoraFin FROM Horario AS H " +
                " WHERE H.ID_Horario NOT IN (SELECT H.ID_Horario FROM PROFESIONAL AS P " +
                " INNER JOIN TURNOS AS T ON T.ID_PROFESIONAL=P.ID_PROFESIONAL" +
                " INNER JOIN HORARIO AS H ON H.ID_HORARIO = T.ID_HORARIO AND P.Id_Profesional = '" + dsprof.Tables["Profesional"].Rows[0][0] + "'" +
                " WHERE CONVERT(VARCHAR,T.Fecha,103) = CONVERT(VARCHAR,'" + fecha + "',103))"+

                " AND CONVERT(varchar,H.HoraIncio,108) >= " +

                " (SELECT CONVERT(varchar,E.Horadesde,108) from Profesional AS pro " +
                " INNER JOIN HorarioProfesional AS Hpro ON pro.Id_Profesional=Hpro.Id_Profesional " +
                " INNER JOIN DiaHorarioLaboral AS DHL ON DHL.HorarioId=Hpro.HorarioId " +
                " INNER JOIN Elemento AS E ON E.DiaId=DHL.DiaId and Hpro.HorarioId=E.HorarioId WHERE Pro.Id_Profesional='" + dsprof.Tables["Profesional"].Rows[0][0] + "'" +
                " AND '" + DiaSem + "'=DHL.Nombre)" +

                " AND CONVERT(varchar,H.HoraIncio,108) <=" +

                " (SELECT CONVERT(varchar,E.HoraHasta,108) from Profesional AS pro " +
                " INNER JOIN HorarioProfesional AS Hpro ON pro.Id_Profesional=Hpro.Id_Profesional" +
                " INNER JOIN DiaHorarioLaboral AS DHL ON DHL.HorarioId=Hpro.HorarioId" +
                " INNER JOIN Elemento AS E ON E.DiaId=DHL.DiaId and Hpro.HorarioId=E.HorarioId WHERE Pro.Id_Profesional='" + dsprof.Tables["Profesional"].Rows[0][0] + "'" +
                " AND '"+DiaSem+"'=DHL.Nombre)";

                SqlDataAdapter daPac = new SqlDataAdapter(consulta, conexion);
                daPac.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                daPac.Fill(dsHora, "Horario");
            }
            catch (Exception ex)
            {

            }


            return dsHora;




        }


        public DataSet ConseguirHorariosConHora(string fecha)
        {
            DataSet dsHora = new DataSet();

            try
            {

                SqlConnection conexion = new SqlConnection(cadenaConexion);
                String consulta = "Select H.id_horario FROM Horario AS H WHERE Convert(varchar,H.horaincio,108) ='" + fecha + "'";
                SqlDataAdapter daPac = new SqlDataAdapter(consulta, conexion);
                daPac.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                daPac.Fill(dsHora, "Horario");
            }
            catch (Exception ex)
            {

            }


            return dsHora;





        }


    }
}
